package com.vecchio.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.vecchio.model.News;
import com.vecchio.uitl.DateUtil;

public class NewsDao {
	public boolean updateNews(Connection connection,News news)throws Exception {
		int row = -1;
		String sql = "update news set newscontent=?,newstitle=?";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setString(1, news.getNewscontent());
		preparedStatement.setString(2, news.getNewstitle());
		row = preparedStatement.executeUpdate();
		if (row>=0) {
			return true;
		}
		return false;
	}
	
	public List<News> getNewsListByUserID(Connection connection,int userid)throws Exception {
		List<News> newsList = null;
		String sql = "select *,users.username from news left join users on news.userid=users.userid where userid=?";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setInt(1, userid);
		ResultSet resultSet = preparedStatement.executeQuery();
		while (resultSet.next()) {
			if (newsList == null) {
				newsList = new ArrayList<News>();
			}
			News news = new News();
			news.setNewsid(resultSet.getInt("newsid"));
			news.setNewscontent(resultSet.getString("newscontent"));
			news.setNewstitle(resultSet.getString("newstitle"));
			news.setNewstimer(DateUtil.formatString(resultSet.getString("newstimer"), "yyyy-MM-dd HH:mm:ss"));
			news.setUserid(resultSet.getInt("userid"));
			news.setUsername(resultSet.getString("username"));
			newsList.add(news);
		}
		return newsList;
	}
	public List<News> getNewsAllList(Connection connection)throws Exception {
		List<News> newsList = null;
		String sql = "select *,user.username from news left join users on news.userid=users.userid";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		ResultSet resultSet = preparedStatement.executeQuery();
		while (resultSet.next()) {
			if (newsList == null) {
				newsList = new ArrayList<News>();
			}
			News news = new News();
			news.setNewsid(resultSet.getInt("newsid"));
			news.setNewscontent(resultSet.getString("newscontent"));
			news.setNewstitle(resultSet.getString("newstitle"));
			news.setNewstimer(DateUtil.formatString(resultSet.getString("newstimer"), "yyyy-MM-dd HH:mm:ss"));
			news.setUserid(resultSet.getInt("userid"));
			news.setUsername(resultSet.getString("username"));
			newsList.add(news);
		}
		return newsList;
	}
	public boolean deleteNewsById(Connection connection,int newsid)throws Exception	 {
		int row = -1;
		String sql = "delete from news where newsid=?";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setInt(1, newsid);
		row = preparedStatement.executeUpdate();
		if (row>0) {
			return true;
		}
		
		
		return false;
		
	}
	public News getNewsById(Connection connection,int newsid)throws Exception {
		String sql = "select *,users.username from news left join users on news.userid=users.userid where newsid=?";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setInt(1, newsid);
		ResultSet resultSet = preparedStatement.executeQuery();
		News news = null;
		if (resultSet.next()) {
			news = new News();
			news.setNewsid(resultSet.getInt("newsid"));
			news.setNewscontent(resultSet.getString("newscontent"));
			news.setNewstitle(resultSet.getString("newstitle"));
			news.setNewstimer(DateUtil.formatString(resultSet.getString("newstimer"), "yyyy-MM-dd HH:mm:ss"));
			news.setUserid(resultSet.getInt("userid"));
			news.setUsername(resultSet.getString("username"));
		}
		return news;
	}
	public boolean addNews(Connection connection,News news)throws SQLException {
		String sql = "insert into news(newscontent,newstimer,userid,newstitle) values(?,?,?,?)";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setString(1, news.getNewscontent());
		preparedStatement.setString(2, DateUtil.formatDate(news.getNewstimer(), "yyyy-MM-dd HH:mm:ss"));
		preparedStatement.setInt(3, news.getUserid());
		preparedStatement.setString(4, news.getNewstitle());
		int row = preparedStatement.executeUpdate();
		if (row>0) {
			return true;
		}
		return false;
	}
}
